﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlServerCe;

namespace Flockmaster
{
    public class SendCmd
    {
        string tablename;
        string sqlcmd;
        bool onlytablestructure=false;

        public string TableName
        {
            get { return tablename; }
            set { tablename = value; }
        }

        public string SqlCmd
        {
            get { return sqlcmd; }
            set { sqlcmd = value; }
        }

        public bool IsOnlyTableStructure
        {
            get { return onlytablestructure; }
            set { onlytablestructure = value; }
        }
    }

    public class DatabaseCopy:IDatabaseCopy
    {
        public event EventHandler<MyEventArgs> ReportsProgress;
        public event EventHandler<MyEventArgs> End;
        public event EventHandler<ErrorEventArgs> ReceiveErrorMeaaage;

        public IDbConnection destConnection;
        public IDbConnection srcConnection;
        public IDbConnection sysConnection;
        protected DBTypes db_Type;
        protected DBTypes dest_Type;
        protected BackgroundWorker BackWorker;
        public int count = 0;
        private static bool IsStop = true;

        public bool ISStop
        {
            get { return IsStop; }
            set { IsStop = value; }
        }

        public int Count
        {
            get { return count; }
            set { count = value; }
        }

        public DBTypes GetSrcType
        {
            get { return db_Type; }
            set { db_Type = value; }
        }

        public DBTypes GetDestType
        {
            get { return dest_Type; }
            set { dest_Type = value; }
        }


        public IDbConnection DestConnection
        {
            get { return destConnection; }
            set { destConnection = value; }
        }

        public IDbConnection SrcConnection
        {
            get { return srcConnection; }
            set { srcConnection = value; }
        }

        public IDbConnection SysConnection
        {
            get { return sysConnection; }
            set { sysConnection = value; }
        }

        //构造函数
        public DatabaseCopy()
        {
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="IpAddress"></param>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <param name="Database">Oracle的时候该字段为服务名</param>
        /// <param name="dbType"></param>
        public void CreateConn(string IpAddress, string Username, string Password, string Database, DBTypes m_dbType, ref IDbConnection Conn)
        {
            string connString = "";
            db_Type = m_dbType;
            switch (m_dbType)
            {
                default:
                    throw new Exception("未知数据库类型");
                case DBTypes.SqlServer:
                    connString = "Data Source=" + IpAddress + ";" +
                        (Database != null && Database != "" ? "Database=" + Database + ";" : "") +
                        "UID=" + Username + ";" +
                        "Password=" + Password + ";";
                    Conn = new SqlConnection(connString);
                    break;

                case DBTypes.Oracle:
                    connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + IpAddress + ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" + Database + ")));User Id=" + Username + ";Password=" + Password + ";";
                    Conn = new OracleConnection(connString);
                    break;

                case DBTypes.MySql:
                    connString = String.Format("server={0};Database={3};uid={1};pwd={2};charset=utf8;", IpAddress, Username, Password, Database);
                    Conn = new MySqlConnection(connString);
                    break;
                case DBTypes.Access:
                    if ((Username == String.Empty && Password == String.Empty) || Username == null && Password == null)
                    {
                        connString = string.Format("provider=microsoft.jet.oledb.4.0;Data Source={0};", IpAddress);
                    }
                    else
                    {
                        connString = string.Format("provider=microsoft.jet.oledb.4.0;Data Source={0};User ID={1};Password={2}", IpAddress, Username, Password);
                    }
                    Conn = new OleDbConnection(connString);
                    break;
                case DBTypes.SQLCE:
                    connString = String.Format("Data Source={0};", IpAddress);
                    if (Database != null && Database != ""){ connString += "Password=" + Password + ";"; }
                    if (Database != null && Database != "") { connString += "Max Database Size=" + Database + ";"; }
                    Conn = new SqlCeConnection(connString);
                    break;
            }
        }

        public void CreateConn(string IpAddress, string Username, string Password, string Database, DBTypes m_dbType, DBConnType Conn)
        {
            IDbConnection IdbConn;
            switch (Conn)
            {
                case DBConnType.SrcConnection:
                    CreateConn(IpAddress, Username, Password, Database, m_dbType, ref srcConnection);
                    break;
                case DBConnType.DestConnection:
                    CreateConn(IpAddress, Username, Password, Database, m_dbType, ref destConnection);
                    break;
                case DBConnType.SysConnection:
                    CreateConn(IpAddress, Username, Password, Database, m_dbType, ref sysConnection);
                    break;
                default:
                    throw new Exception("未知数据库连接！");

            }
        }

        /// 创建系统连接
        /// </summary>
        /// <param name="IpAddress"></param>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <param name="m_dbType"></param>
        public void CreateSysConn(string IpAddress, string Username, string Password, DBTypes m_dbType)
        {
            string Database = "";
            switch (m_dbType)
            {
                default:
                    throw new Exception("未知数据库类型");
                case DBTypes.SqlServer:
                    CreateConn(IpAddress, Username, Password, null, m_dbType, ref sysConnection);
                    break;

                case DBTypes.Oracle:
                    throw new Exception("该方法重载不支持Oracle数据库");

                case DBTypes.MySql:
                    Database = "information_schema";
                    CreateConn(IpAddress, Username, Password, Database, m_dbType, ref sysConnection);
                    break;
                case DBTypes.Access:
                    CreateConn(IpAddress, Username, Password, null,  m_dbType, ref sysConnection);
                    break;
                case DBTypes.SQLCE:
                    CreateConn(IpAddress,null,Password,Username,m_dbType,ref sysConnection);
                    break;
            }
        }

        public void CreateSysConn(string IpAddress, string Username, string Password, DBTypes m_dbType, string ServieName)
        {
            CreateConn(IpAddress, Username, Password, ServieName, m_dbType, ref sysConnection);
        }

        /// <summary>
        /// 获得指定表的行数
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int GetRowsCount(string TableName)
        {
            //*********此处这样是为了在避免在上一个表复制过程中异常，导致连接状态显示Open但是不能正常工作的情况
            srcConnection.Close();
            srcConnection.Open();
            //*************************************************************************************************
            int Count = 0;
            using (IDbCommand cmd = srcConnection.CreateCommand())
            {
                cmd.CommandText = String.Format("Select count(*) from {0}", TableName);
                Count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
            }
            return Count;
        }

        /// <summary>
        /// 判断目标数据库指定的表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public virtual bool DoesTableExist(string TableName, DBTypes m_dbType)
        {
            DataTable SchemaTable =new DataTable();
            switch (m_dbType)
            {
                case DBTypes.SqlServer:
                    SchemaTable = ((SqlConnection)destConnection).GetSchema("Tables", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.Oracle:
                    SchemaTable = ((OracleConnection)destConnection).GetSchema("Tables", new string[] { null, TableName.ToUpper() });
                    break;
                case DBTypes.MySql:
                    SchemaTable = ((MySqlConnection)destConnection).GetSchema("Tables", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.Access:
                    SchemaTable = ((OleDbConnection)destConnection).GetSchema("Tables", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.SQLCE:
                    IDbCommand SqlCmd = destConnection.CreateCommand();
                    SqlCmd.CommandText = "SELECT  count(TABLE_NAME)   FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + TableName + "'";
                    return SqlCmd.ExecuteScalar().Equals(1);

            }
            bool IsTrue = false;
            if (SchemaTable.Rows.Count > 0)
            {
                IsTrue = true;
            }
            return IsTrue;
        }

        /// <summary>
        /// 删除指定的表
        /// </summary>
        /// <param name="TableName"></param>
        public virtual void DropTable(string TableName)
        {
            using (IDbCommand sqlCmd = this.destConnection.CreateCommand())
            {
                sqlCmd.CommandText = "drop table " + TableName.ToUpper();
                sqlCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 获得所有的库
        /// </summary>
        /// <param name="Conn"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public ArrayList GetDataBaseList(IDbConnection Conn, DBTypes dbType)
        {
            if (dbType == DBTypes.Access)
            {
                throw new Exception("Access数据文件不支持该方法！");
                return null;
            }
            if (sysConnection.State != ConnectionState.Open) { sysConnection.Open(); }
            ArrayList dbNameList = new ArrayList();
            string dbName = "";
            using (IDbCommand SqlCmd = Conn.CreateCommand())
            {
                switch (dbType)
                {
                    default:
                        throw new Exception("未知数据库类型");
                    case DBTypes.SqlServer:

                        SqlCmd.CommandText = "SELECT name from master.dbo.sysdatabases";
                        IDataReader sdr = SqlCmd.ExecuteReader();
                        while (sdr.Read())
                        {
                            // 添加数据库名
                            dbNameList.Add(sdr[0].ToString());
                        }
                        sdr.Close();
                        sdr.Dispose();
                        break;

                    case DBTypes.Oracle:

                        throw new Exception("该方法不支持Oracle数据库！");

                    case DBTypes.MySql:
                        SqlCmd.CommandText = "SHOW DATABASES";
                        IDataReader dbDr = SqlCmd.ExecuteReader();
                        while (dbDr.Read())
                        {
                            dbName = dbDr[0].ToString();
                            dbNameList.Add(dbName);
                        }
                        dbDr.Close();
                        dbDr.Dispose();
                        break;
                }
            }
            return dbNameList;
        }

        /// <summary>
        /// 获得数据库下的表名
        /// </summary>
        /// <param name="dbConn">建议使用GetDbConnection获得</param>
        /// <returns></returns>
        public ArrayList GetTableList(string Database, DBTypes dbType, string username)
        {
            if (sysConnection.State != ConnectionState.Open) { sysConnection.Open(); }
            ArrayList array = new ArrayList();
            DataTable SchemaTable = new DataTable();
            using (IDbCommand SqlCmd = sysConnection.CreateCommand())
            {
                switch (db_Type)
                {
                    default:
                        throw new Exception("未知数据库类型");
                    case DBTypes.SqlServer:
                        SqlCmd.CommandText = @"USE " + Database + "\r\n SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
                        break;
                    case DBTypes.Oracle:
                        SqlCmd.CommandText = "select TABLE_NAME from SYS.ALL_TABLES where OWNER='" + username.ToUpper() + "'";
                        break;

                    case DBTypes.MySql:
                        SqlCmd.CommandText = "select TABLE_NAME from TABLES where TABLE_SCHEMA='" + Database + "'";
                        break;
                    case DBTypes.Access:
                        SchemaTable = ((OleDbConnection)sysConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "Table" });
                        for (int i = 0; i < SchemaTable.Rows.Count; i++)
                        {
                            array.Add(SchemaTable.Rows[i]["TABLE_NAME"].ToString());
                        }
                        return array;
                    case DBTypes.SQLCE:
                        SqlCmd.CommandText = "SELECT  TABLE_NAME   FROM   INFORMATION_SCHEMA.TABLES";
                        break;
                }
                try
                {
                    IDataReader dr = SqlCmd.ExecuteReader();
                    while (dr.Read())
                    {
                        array.Add(dr[0].ToString());
                    }
                    dr.Close();
                    dr.Dispose();
                }
                catch (DataException ex)
                {
                    throw new Exception("GetTableList出错！" + ex.Message);
                }

            }
            return array;
        }

        public ArrayList GetTableList(string Database, DBTypes dbType)
        {
            if (db_Type != DBTypes.SqlServer && db_Type != DBTypes.MySql)
            {
                throw new Exception("该方法的重载只能被用于ACCESS数据库！");
            }
            return GetTableList(Database, db_Type, null);
        }

        public ArrayList GetTableList(DBTypes dbType)
        {
            if (db_Type != DBTypes.Access && db_Type != DBTypes.SQLCE)
            {
                throw new Exception("该方法的重载只能被用于ACCESS数据库！");
            }
            return GetTableList(null, db_Type, null);
        }


        /// <summary>
        /// 获取指定表下的所有列
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="dbType"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public ArrayList GetColumnList(string TableName, DBTypes dbType, string username)
        {
            if (srcConnection.State != ConnectionState.Open) { srcConnection.Open(); }
            ArrayList array = new ArrayList();
            DataTable SchemaTable = new DataTable();

            switch (db_Type)
            {
                case DBTypes.MySql:
                    SchemaTable = ((MySqlConnection)srcConnection).GetSchema("Columns", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.SqlServer:
                    SchemaTable = ((SqlConnection)srcConnection).GetSchema("Columns", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.Oracle:
                    SchemaTable = ((OracleConnection)srcConnection).GetSchema("Columns", new string[] { null, null, TableName, null });
                    break;
                case DBTypes.Access:
                    SchemaTable = ((OleDbConnection)sysConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, TableName, null });
                    break;
                case DBTypes.SQLCE:
                    SqlCeCommand cmd = ((SqlCeConnection)srcConnection).CreateCommand();
                    cmd.CommandText = "SELECT  *   FROM   INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='" + TableName + "'";
                    SqlCeDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        array.Add(sdr["COLUMN_NAME"].ToString());
                    }

                    return array;
                default:
                    throw new Exception("未知数据库类型");
                    break;
            }
            for (int i = 0; i < SchemaTable.Rows.Count; i++)
            {
                array.Add(SchemaTable.Rows[i]["COLUMN_NAME"].ToString());
            }
            return array;

        }

        /// <summary>
        /// 停止同步进程
        /// </summary>
        public void StopCopySession()
        {
            ISStop = false;
            if (BackWorker != null)
            {
                if (BackWorker.IsBusy)
                {

                    BackWorker.CancelAsync();
                    BackWorker.Dispose();
                }
            }
        }


        /// <summary>
        /// 拷贝指定的表*******************
        /// </summary>
        /// <param name="TableName"></param>
        public virtual void CopyTable(SendCmd Message)
        {
            BackWorker = new BackgroundWorker();
            BackWorker.WorkerSupportsCancellation = true;
            BackWorker.DoWork += new DoWorkEventHandler(BackWorker_DoWork);
            BackWorker.ProgressChanged += new ProgressChangedEventHandler(BackWorker_ProgressChanged);
            BackWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(BackWorker_RunWorkerCompleted);
            BackWorker.WorkerReportsProgress = true;
            BackWorker.RunWorkerAsync(Message);
        }

        protected virtual void BackWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            //throw new NotImplementedException();
        }

        protected virtual void BackWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            EventHandler<MyEventArgs> temp = End;
            if (temp != null) temp(this, new MyEventArgs(0));
        }

        protected virtual void BackWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            switch ((int)e.ProgressPercentage)
            {
                case (int)MessageTypes.ErrorMessage:
                    EventHandler<ErrorEventArgs> error = ReceiveErrorMeaaage;
                    if (error != null) error(this, new ErrorEventArgs(e.UserState.ToString()));
                    break;
                default:
                    EventHandler<MyEventArgs> temp = ReportsProgress;
                    if (temp != null) temp(this, new MyEventArgs(e.ProgressPercentage));
                    break;
            }
        }


        /// <summary>
        /// 生成创建数据库表结构的SQL语句。*******************
        /// </summary>
        protected virtual string GenerateTableSchemaSql(DataTable schemaTable, ref bool IsKey)
        {
            //构造创建的SQL语句
            return null;
        }

        /// <summary>
        /// 抓取目标数据库里字段类型***************
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        protected virtual string GetDataType(DataTable scheamTable, int i)
        {
            return null;
        }

        /// <summary>
        /// 从 .NET 数据类型获取对应的 MySql  类型名称。***********
        /// </summary>
        protected virtual string GetNativeType(string typeName)
        {
            return null;
        }
    }
}
